package com.dailyblue.javaee.dao;

import com.dailyblue.javaee.bean.Emp;
import com.dailyblue.javaee.db.DBManager;
import lombok.SneakyThrows;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class EmpDAO {
    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    @SneakyThrows
    public List<Emp> find() {
        List<Emp> list = new ArrayList<>();
        // 1. 连接
        conn = DBManager.getConnection();
        // 2. 执行SQL
        String sql = "select * from emp where state=1";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        // 3. 封装数据
        while (rs.next()) {
            Emp emp = new Emp();
            emp.setEmpno(rs.getInt(1));
            emp.setEnname(rs.getString(2));
            emp.setJob(rs.getString(3));
            emp.setMgr(rs.getInt(4));
            emp.setHireDate(rs.getString(5));
            emp.setSal(rs.getDouble(6));
            emp.setComm(rs.getDouble(7));
            emp.setDeptno(rs.getInt(8));
            emp.setState(1);
            list.add(emp);
        }
        return list;
    }
    @SneakyThrows
    public List<Emp> find(int page) {
        List<Emp> list = new ArrayList<>();
        // 1. 连接
        conn = DBManager.getConnection();
        // 2. 执行SQL
        int start = (page - 1) * 10;
        String sql = "SELECT *FROM emp e LEFT JOIN dept d on e.deptno=d.deptno  " +
                "LEFT JOIN emp e2 on e.mgr =e2.empno " +
                "WHERE  e.state =1  limit ?,10";
        ps = conn.prepareStatement(sql);
        ps.setInt(1, start);
        rs = ps.executeQuery();
        // 3. 封装数据
        while (rs.next()) {
            Emp emp = new Emp();
            emp.setEmpno(rs.getInt(1));
            emp.setEnname(rs.getString(2));
            emp.setJob(rs.getString(3));
            emp.setMgr(rs.getInt(4));
            emp.setHireDate(rs.getString(5));
            emp.setSal(rs.getDouble(6));
            emp.setComm(rs.getDouble(7));
            emp.setDeptno(rs.getInt(8));
            emp.setMgrEnname(rs.getString(14));
            emp.setDnname(rs.getString(11));
            emp.setState(1);
            list.add(emp);
        }
        return list;
    }
    @SneakyThrows
    public int findAllCount() {
        int count = 0;
        conn = DBManager.getConnection();
        String sql = "select count(*) from emp where state=1";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        if (rs.next()) {
            count = rs.getInt(1);
        }
        return count;
    }

    @SneakyThrows
    public int findAllCount(String enname) {
        int count = 0;
        conn = DBManager.getConnection();
        String sql = "select count(*) from emp where state=1 and enname like ?";
        ps = conn.prepareStatement(sql);
        ps.setString(1, "%" + enname + "%");
        rs = ps.executeQuery();
        if (rs.next()) {
            count = rs.getInt(1);
        }
        return count;
    }

    @SneakyThrows
    public void delete(int id) {
        conn = DBManager.getConnection();
        String sql = "update emp set state=0 where empno=?";
        ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        ps.executeUpdate();
    }

    @SneakyThrows
    public List<Emp> findByEnname(String enname, int page) {
        List<Emp> list = new ArrayList<>();
        // 1. 连接
        conn = DBManager.getConnection();
        // 2. 执行SQL
        int start = (page - 1) * 10;
        String sql = "select * from \n" +
                "\temp e left join dept d on e.deptno=d.DEPTNO \n" +
                "\tleft join emp e2 on e.mgr=e2.empno\n" +
                "\twhere e.state=1; and e.enname like ? limit ?,10";
        ps = conn.prepareStatement(sql);
        ps.setString(1, "%" + enname + "%");
        ps.setInt(2, start);
        rs = ps.executeQuery();
        // 3. 封装数据
        while (rs.next()) {
            Emp emp = new Emp();
            emp.setEmpno(rs.getInt(1));
            emp.setEnname(rs.getString(2));
            emp.setJob(rs.getString(3));
            emp.setMgr(rs.getInt(4));
            emp.setHireDate(rs.getString(5));
            emp.setSal(rs.getDouble(6));
            emp.setComm(rs.getDouble(7));
            emp.setDeptno(rs.getInt(8));
            emp.setState(1);
            list.add(emp);
        }
        return list;
    }

}